--    Program: 		CAPPOIMP_LOCATION_B.sql
--    Description:  Import PO LOCATION information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         27-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_location_id			number;
                    		                 		
   cursor cur_LOCATION is
      select * from CRST_HR_LOCATIONS_ALL
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_LOCATION loop   	
     l_err_msg_tmp := '';
     
     SELECT HR_LOCATIONS_S.NEXTVAL 
     INTO l_LOCATION_id
     FROM DUAL;
     
     update CRST_HR_LOCATIONS_ALL 
     set    location_id_n = l_location_id
     where  location_id = cur.LOCATION_ID;
     
 	 update HR_LOCATIONS_ALL
 	 set LOCATION_CODE = CAPBRIMP_MAPPING.RenameStr(Substr(LOCATION_CODE,5,4),LOCATION_CODE)
 	 WHERE LOCATION_ID = CUR.LOCATION_ID;
 	 
     begin
       -- import data
       insert into HR_LOCATIONS_ALL
       (
         LOCATION_ID                ,
         LOCATION_CODE              ,
         BUSINESS_GROUP_ID          ,
         DESCRIPTION                ,
         SHIP_TO_LOCATION_ID        ,
         SHIP_TO_SITE_FLAG          ,
         RECEIVING_SITE_FLAG        ,
         BILL_TO_SITE_FLAG          ,
         IN_ORGANIZATION_FLAG       ,
         OFFICE_SITE_FLAG           ,
         DESIGNATED_RECEIVER_ID     ,
         INVENTORY_ORGANIZATION_ID  ,
         TAX_NAME                   ,
         INACTIVE_DATE              ,
         STYLE                      ,
         ADDRESS_LINE_1             ,
         ADDRESS_LINE_2             ,
         ADDRESS_LINE_3             ,
         TOWN_OR_CITY               ,
         COUNTRY                    ,
         POSTAL_CODE                ,
         REGION_1                   ,
         REGION_2                   ,
         REGION_3                   ,
         TELEPHONE_NUMBER_1         ,
         TELEPHONE_NUMBER_2         ,
         TELEPHONE_NUMBER_3         ,
         LOC_INFORMATION13          ,
         LOC_INFORMATION14          ,
         LOC_INFORMATION15          ,
         LOC_INFORMATION16          ,
         LOC_INFORMATION17          ,
         ATTRIBUTE_CATEGORY         ,
         ATTRIBUTE1                 ,
         ATTRIBUTE2                 ,
         ATTRIBUTE3                 ,
         ATTRIBUTE4                 ,
         ATTRIBUTE5                 ,
         ATTRIBUTE6                 ,
         ATTRIBUTE7                 ,
         ATTRIBUTE8                 ,
         ATTRIBUTE9                 ,
         ATTRIBUTE10                ,
         ATTRIBUTE11                ,
         ATTRIBUTE12                ,
         ATTRIBUTE13                ,
         ATTRIBUTE14                ,
         ATTRIBUTE15                ,
         ATTRIBUTE16                ,
         ATTRIBUTE17                ,
         ATTRIBUTE18                ,
         ATTRIBUTE19                ,
         ATTRIBUTE20                ,
         GLOBAL_ATTRIBUTE_CATEGORY  ,
         GLOBAL_ATTRIBUTE1          ,
         GLOBAL_ATTRIBUTE2          ,
         GLOBAL_ATTRIBUTE3          ,
         GLOBAL_ATTRIBUTE4          ,
         GLOBAL_ATTRIBUTE5          ,
         GLOBAL_ATTRIBUTE6          ,
         GLOBAL_ATTRIBUTE7          ,
         GLOBAL_ATTRIBUTE8          ,
         GLOBAL_ATTRIBUTE9          ,
         GLOBAL_ATTRIBUTE10         ,
         GLOBAL_ATTRIBUTE11         ,
         GLOBAL_ATTRIBUTE12         ,
         GLOBAL_ATTRIBUTE13         ,
         GLOBAL_ATTRIBUTE14         ,
         GLOBAL_ATTRIBUTE15         ,
         GLOBAL_ATTRIBUTE16         ,
         GLOBAL_ATTRIBUTE17         ,
         GLOBAL_ATTRIBUTE18         ,
         GLOBAL_ATTRIBUTE19         ,
         GLOBAL_ATTRIBUTE20         ,
         LAST_UPDATE_DATE           ,
         LAST_UPDATED_BY            ,
         LAST_UPDATE_LOGIN          ,
         CREATED_BY                 ,
         CREATION_DATE              ,
         ENTERED_BY                 ,
         TP_HEADER_ID               ,
         ECE_TP_LOCATION_CODE       ,
         OBJECT_VERSION_NUMBER      ,
       --GEOMETRY                   ,
         LOC_INFORMATION18          ,
         LOC_INFORMATION19          ,
         LOC_INFORMATION20          ,
         DERIVED_LOCALE  
       )
       values
       (
         l_location_id                  ,
         cur.LOCATION_CODE              ,
         CAPBRIMP_MAPPING.GetBGMapping(cur.BUSINESS_GROUP_ID),
         cur.DESCRIPTION                ,
         l_location_id                  ,
         cur.SHIP_TO_SITE_FLAG          ,
         cur.RECEIVING_SITE_FLAG        ,
         cur.BILL_TO_SITE_FLAG          ,
         cur.IN_ORGANIZATION_FLAG       ,
         cur.OFFICE_SITE_FLAG           ,
         cur.DESIGNATED_RECEIVER_ID     ,
         CAPBRIMP_MAPPING.GetOrganizationMapping(cur.INVENTORY_ORGANIZATION_ID),
         cur.TAX_NAME                   ,
         cur.INACTIVE_DATE              ,
         cur.STYLE                      ,
         cur.ADDRESS_LINE_1             ,
         cur.ADDRESS_LINE_2             ,
         cur.ADDRESS_LINE_3             ,
         cur.TOWN_OR_CITY               ,
         cur.COUNTRY                    ,
         cur.POSTAL_CODE                ,
         cur.REGION_1                   ,
         cur.REGION_2                   ,
         cur.REGION_3                   ,
         cur.TELEPHONE_NUMBER_1         ,
         cur.TELEPHONE_NUMBER_2         ,
         cur.TELEPHONE_NUMBER_3         ,
         cur.LOC_INFORMATION13          ,
         cur.LOC_INFORMATION14          ,
         cur.LOC_INFORMATION15          ,
         cur.LOC_INFORMATION16          ,
         cur.LOC_INFORMATION17          ,
         cur.ATTRIBUTE_CATEGORY         ,
         cur.ATTRIBUTE1                 ,
         cur.ATTRIBUTE2                 ,
         cur.ATTRIBUTE3                 ,
         cur.ATTRIBUTE4                 ,
         cur.ATTRIBUTE5                 ,
         cur.ATTRIBUTE6                 ,
         cur.ATTRIBUTE7                 ,
         cur.ATTRIBUTE8                 ,
         cur.ATTRIBUTE9                 ,
         cur.ATTRIBUTE10                ,
         cur.ATTRIBUTE11                ,
         cur.ATTRIBUTE12                ,
         cur.ATTRIBUTE13                ,
         cur.ATTRIBUTE14                ,
         cur.ATTRIBUTE15                ,
         cur.ATTRIBUTE16                ,
         cur.ATTRIBUTE17                ,
         cur.ATTRIBUTE18                ,
         cur.ATTRIBUTE19                ,
         cur.ATTRIBUTE20                ,
         cur.GLOBAL_ATTRIBUTE_CATEGORY  ,
         cur.GLOBAL_ATTRIBUTE1          ,
         cur.GLOBAL_ATTRIBUTE2          ,
         cur.GLOBAL_ATTRIBUTE3          ,
         cur.GLOBAL_ATTRIBUTE4          ,
         cur.GLOBAL_ATTRIBUTE5          ,
         cur.GLOBAL_ATTRIBUTE6          ,
         cur.GLOBAL_ATTRIBUTE7          ,
         cur.GLOBAL_ATTRIBUTE8          ,
         cur.GLOBAL_ATTRIBUTE9          ,
         cur.GLOBAL_ATTRIBUTE10         ,
         cur.GLOBAL_ATTRIBUTE11         ,
         cur.GLOBAL_ATTRIBUTE12         ,
         cur.GLOBAL_ATTRIBUTE13         ,
         cur.GLOBAL_ATTRIBUTE14         ,
         cur.GLOBAL_ATTRIBUTE15         ,
         cur.GLOBAL_ATTRIBUTE16         ,
         cur.GLOBAL_ATTRIBUTE17         ,
         cur.GLOBAL_ATTRIBUTE18         ,
         cur.GLOBAL_ATTRIBUTE19         ,
         cur.GLOBAL_ATTRIBUTE20         ,
         cur.LAST_UPDATE_DATE           ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
         cur.LAST_UPDATE_LOGIN          ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
         cur.CREATION_DATE              ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.ENTERED_BY),
         cur.TP_HEADER_ID               ,
         cur.ECE_TP_LOCATION_CODE       ,
         cur.OBJECT_VERSION_NUMBER      ,
       --cur.GEOMETRY                   ,
         cur.LOC_INFORMATION18          ,
         cur.LOC_INFORMATION19          ,
         cur.LOC_INFORMATION20          ,
         cur.DERIVED_LOCALE  
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_LOCATION_B', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
